IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCOIInProgress]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCOIInProgress]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO    
/******************************************************************      
* Name:   [GetCOIInProgress]      
* Purpose:  Retrieves the COI with 'COIStatus_Start' or 'COIStatus_Inprogress' status for the passed in personId     
*			At any point of time only one status(start or in progress will be present)
* PARAMETERS      
* Name         Description           
* -------------      -------------------------------------------      
* @admPersonId     Person Id for whom the latest COI should be returned     
******************************************************************/      
      
CREATE Procedure [dbo].[GetCOIInProgress] (      
  @admPersonId  INTEGER       
)      
AS      
BEGIN      
      
 SET NOCOUNT ON      
      
 Select COI.Id,      
  AdmPersonId,      
  FwkDomainUserId,      
  LastNotified,      
  LastModified,      
  DateSubmitted,      
  DateReviewed,      
  HasOutsideActivities,      
  HasEquityInterests,      
  HasFamilyAssociations,
  Royalty,      
  OtherActivity,      
  PolicyViolation,      
  AggregateEquity,      
  Status,      
  RefCode.RefMeaning as StatusMeaning,       
  DocumentId,
  ReportingPeriodId,
  ReportingPeriod      
 FROM ConflictOfInterest COI INNER JOIN RefCode ON COI.Status = RefCode.RefCode  
  LEFT JOIN COIReportingPeriods CRP ON CRP.Id = COI.ReportingPeriodId    
 WHERE AdmPersonId = @admPersonId    
 AND    
   Status in ('COISTATUS_START','COISTATUS_INPROGRESS')       
END 